package org.oschina.util;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

public class JdbcTemplateUtil {

	/**
	 * 批量更新
	 * @param sql
	 * @param args
	 * @param jdbcTemplate
	 * @return
	 */
	public static int[] batchUpdate(String sql,List<Object[]> args,JdbcTemplate jdbcTemplate)
	{
		return jdbcTemplate.batchUpdate(sql,args);
	}
	
	/**
	 * 查询bean
	 * @param sql
	 * @param args
	 * @param type
	 * @param jdbcTemplate
	 * @return
	 */
	public static Object queryForBean(String sql,Object args[],Class type,JdbcTemplate jdbcTemplate)
	{
		return getFirst(query(sql,args,type,jdbcTemplate));
	}
	
	/**
	 * 查询对象
	 * @param sql
	 * @param args
	 * @param type
	 * @param jdbcTemplate
	 * @return
	 */
	public static Object queryForObject(String sql,Object args[],Class type,JdbcTemplate jdbcTemplate)
	{
		return getFirst(queryForList(sql,args,type,jdbcTemplate));
	}
	
	/**
	 * 返回列表
	 * @param sql
	 * @param args
	 * @param type
	 * @param jdbcTemplate
	 * @return
	 */
	public static List<Object> queryForList(String sql,Object args[],Class type,JdbcTemplate jdbcTemplate)
	{
		return jdbcTemplate.queryForList(sql,type,args);
	}
	
	/**
	 * 查询
	 * @param sql
	 * @param args
	 * @param type
	 * @param jdbcTemplate
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static List query(String sql,Object args[],final Class type,JdbcTemplate jdbcTemplate) throws RuntimeException
	{
		//ParameterizedBeanPropertyRowMapper.newInstance(propertyType);
		
		return jdbcTemplate.query(sql,args,new RowMapper()
		{
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException
			{
				try
				{
					//普通类型直接返回
					if(ReflectUtil.isBasic(type))
						return rs.getObject(1);
					
					Object instance=type.newInstance();
					
					//封装
					for(int i=1,len=rs.getMetaData().getColumnCount();i<=len;i++)
					{
						String columnName=rs.getMetaData().getColumnLabel(i);
						setProperty(instance,columnName,type,rs.getObject(i));
					}
					return instance;
				}
				catch(Exception e)
				{
					throw new RuntimeException(e);
				}
			}
			
			private void setProperty(Object instance,String columnName,Class type,Object value) throws IllegalAccessException, InvocationTargetException, InstantiationException, NoSuchFieldException, SecurityException, NoSuchMethodException
			{
				String splitName[]=StringUtils.split(columnName, ".");
				if(splitName.length==1)
					BeanUtils.setProperty(instance,columnName,value);
				else
				{
					String attributeName=columnName.substring(columnName.indexOf(".")+1,columnName.length());
					Class fieldClass=type.getDeclaredField(splitName[0]).getType();
					Object fieldInstance=PropertyUtils.getProperty(instance,splitName[0]);
					if(fieldInstance==null)
					{
						fieldInstance=fieldClass.newInstance();
						PropertyUtils.setProperty(instance,splitName[0],fieldInstance);
					}
					setProperty(fieldInstance,attributeName,fieldClass,value);
				}
			}
		});
	}
	
	/**
	 * 更新
	 * @param sql
	 * @param args
	 * @param jdbcTemplate
	 * @return 插入操作返回更改的主键  更新/删除操作返回影响行数
	 */
	public static int update(final String sql,final Object args[],JdbcTemplate jdbcTemplate)
	{
		KeyHolder keyHolder=new GeneratedKeyHolder();
		int result=0;
		result=jdbcTemplate.update(new PreparedStatementCreator()
		{
			public PreparedStatement createPreparedStatement(Connection connection)throws SQLException
			{
				PreparedStatement preparedStatement=connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				if(args!=null && args.length>0)
				{
					for(int i=0;i<args.length;i++)
						preparedStatement.setObject(i+1,args[i]);
				}
				return preparedStatement;
			}
		},keyHolder);
		if(keyHolder.getKey()!=null)
			result=keyHolder.getKey().intValue();
		return result;
	}
	
	/**
	 * 返回列表第一条数据
	 * @param list
	 * @return
	 */
	private static Object getFirst(List list)
	{
		if(list==null || list.size()==0)
			return null;
		return list.get(0);
	}
}